package com.css.fxfzypg.modules.saresults.repository.impl;

import com.css.fxfzypg.modules.saresults.entity.*;
import com.css.fxfzypg.modules.saresults.param.AdminUpdateParam;
import com.css.fxfzypg.modules.saresults.param.ApproveParam;
import com.css.fxfzypg.modules.saresults.repository.SaResultsRepository;
import com.css.fxfzypg.modules.saresults.vo.HistoryVo;
import com.css.fxfzypg.modules.saresults.vo.SaResultsHlVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

@Repository
@Slf4j
public class SaResultsRepositoryImpl implements SaResultsRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SysAreaService sysAreaService;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void saveApAsicMap(DzqhApAsicMapEntity it) {
//        jdbcTemplate.update("insert into dzqh_ap_basic_map(project_id,project_name,survey,administrative_region,sub_date," +
//                        "review_unit,undertaker,project_type,safety_rating,control_points,evaluation_results,registration_unit,rs_map,fp_map,nfss_map," +
//                        "reed_map,nfeed_map,psa_map,bh_map,seismic_pz,bd_map,eg_profile,sagh_evaluation,lp_picture,aosp_file,periodic_point,rs_value," +
//                        "Update_reason,upload_report,create_user,create_time,is_delete,client,project_type_remarks,cp_file,sub_status,registrant,longitude,latitude,approval_status,status,update_status,original_data_id,province_id,org_id,model,geological_profile_image,update_detailed_information,log_id,compare," +
//                        "geom_text)" +
//                        " values ('"+it.getProjectId()+"', '"+it.getProjectName()+"', '"+it.getSurvey()+"'," +
//                "                '"+it.getAdministrativeRegion()+"', '"+it.getSubDate()+"', '"+it.getReviewUnit()+"', '"+it.getUndertaker()+"', '"+it.getProjectType()+"', '"+it.getSafetyRating()+"', '"+it.getControlPoints()+"', '"+it.getEvaluationResults()+"'," +
//                "                '"+it.getRegistrationUnit()+"', '"+it.getRsMap()+"', '"+it.getFpMap()+"', '"+it.getNfssMap()+"', '"+it.getReedMap()+"', '"+it.getNfeedMap()+"', '"+it.getPsaMap()+"', '"+it.getBhMap()+"', '"+it.getSeismicPz()+"', '"+it.getBdMap()+"'," +
//                "                '"+it.getEgProfile()+"', '"+it.getSaghEvaluation()+"', '"+it.getLpPicture()+"', '"+it.getAospFile()+"', '"+it.getPeriodicPoint()+"', '"+it.getRsValue()+"', '"+it.getUpdateReason()+"', '"+it.getUploadReport()+"', '"+it.getCreateUser()+"'," +
//                "                '"+it.getCreateTime()+"', '"+it.getIsDelete()+"', '"+it.getClient()+"', '"+it.getProjectTypeRemarks()+"', '"+it.getCpFile()+"', '"+it.getSubStatus()+"', '"+it.getRegistrant()+"'," +
//                "                '"+it.getLongitude()+"', '"+it.getLatitude()+"', '"+it.getApprovalStatus()+"', '"+it.getStatus()+"', '"+it.getUpdateStatus()+"', '"+it.getOriginalDataId()+"', '"+it.getProvinceId()+"', '"+it.getOrgId()+"', '"+it.getModel()+"', '"+it.getGeologicalProfileImage()+"','"+it.getUpdateDetailedInformation()+"','"+it.getLogId()+"','"+it.getCompare()+"'," +
//                "                '"+it.getGeomText()+"')");
       jdbcTemplate.update("insert into dzqh_ap_basic_map  (project_id,project_name,survey,administrative_region,review_unit,undertaker,project_type,safety_rating,control_points,evaluation_results,registration_unit,rs_map,fp_map,nfss_map,reed_map,nfeed_map,psa_map,bh_map,seismic_pz,bd_map,eg_profile,sagh_evaluation,lp_picture,aosp_file,periodic_point,rs_value,upload_report,create_user,create_time,update_user,update_reason,update_time,is_delete,client,project_type_remarks,cp_file,status,delete_reason,original_data_id,sub_status,registrant,approval_status,review_comments,longitude,latitude,update_status,approval_user,approval_time,approval_org,sub_date,province_id,org_id,model,geom_text,geological_profile_image,update_detailed_information,log_id,compare ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )",
                it.getProjectId(),it.getProjectName(),it.getSurvey(),it.getAdministrativeRegion(),it.getReviewUnit(),it.getUndertaker(),it.getProjectType(),it.getSafetyRating(),it.getControlPoints(),it.getEvaluationResults(),it.getRegistrationUnit(),it.getRsMap(),it.getFpMap(),it.getNfssMap(),it.getReedMap(),it.getNfeedMap(),it.getPsaMap(),it.getBhMap(),it.getSeismicPz(),it.getBdMap(),it.getEgProfile(),it.getSaghEvaluation(),it.getLpPicture(),it.getAospFile(),it.getPeriodicPoint(),it.getRsValue(),it.getUploadReport(),it.getCreateUser(),it.getCreateTime(),it.getUpdateUser(),it.getUpdateReason(),it.getUpdateTime(),it.getIsDelete(),it.getClient(),it.getProjectTypeRemarks(),it.getCpFile(),it.getStatus(),it.getDeleteReason(),it.getOriginalDataId(),it.getSubStatus(),it.getRegistrant(),it.getApprovalStatus(),it.getReviewComments(),it.getLongitude(),it.getLatitude(),it.getUpdateStatus(),it.getApprovalUser(),it.getApprovalTime(),it.getApprovalOrg(),it.getSubDate(),it.getProvinceId(),it.getOrgId(),it.getModel(),it.getGeomText(),it.getGeologicalProfileImage(),it.getUpdateDetailedInformation(),it.getLogId(),it.getCompare());
    }

    @Override
    public void saveApCruxDh(DzqhApCruxDhEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_dh(geotechnical_id,project_id,geotechnical_name,st_number,lb_depth,geotechnical_density,layer_thickness," +
                        "sw_velocity,del_flag)values (?,?,?,?,?,?,?,?,?)", it.getGeotechnicalId(), it.getProjectId(), it.getGeotechnicalName(),
                it.getStNumber(), it.getLbDepth(), it.getGeotechnicalDensity(), it.getLayerThickness(), it.getSwVelocity(),it.getDelFlag());
    }

    @Override
    public void saveApCzruxEl(DzqhApCruxElEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_el(test_id,project_id,test_number,test_ar,construction_unit,start_longitude,start_latitude,end_longitude,end_latitude," +
                        "test_length,pposl_map,survey_method,survey_parameters,del_flag)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", it.getTestId(), it.getProjectId(), it.getTestNumber(), it.getTestAr(), it.getConstructionUnit(),
                it.getStartLongitude(), it.getStartLatitude(), it.getEndLongitude(), it.getEndLatitude(), it.getTestLength(), it.getPposlMap(), it.getSurveyMethod(), it.getSurveyParameters(),it.getDelFlag());
    }

    @Override
    public void saveApCruxHec(DzqhApCruxHecEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_hec(ec_id,project_id,eo_time_ymd,eo_time_hms,north_latitude,east_longitude,magnitude,depth,epicentral_intensity,accuracy," +
                        "erp_name,del_flag)values (?,?,?,?,?,?,?,?,?,?,?,?)", it.getEcId(), it.getProjectId(), it.getEoTimeYmd(), it.getEoTimeHms(),
                it.getNorthLatitude(), it.getEastLongitude(), it.getMagnitude(), it.getDepth(), it.getEpicentralIntensity(), it.getAccuracy(), it.getErpName(),it.getDelFlag());

    }

    @Override
    public void saveApCruxLayerPf(DzqhApCruxLayerPfEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_layer_pf(pf_id,project_id,borehole_number,hl_longitude,hl_latitude,drilling_depth,ground_elevation,groundwater_level,create_user,create_time,is_delete,project_name,data_type,name)" +
                        "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", it.getPfId(), it.getProjectId(), it.getBoreholeNumber(), it.getHlLongitude(), it.getHlLatitude(), it.getDrillingDepth(), it.getGroundElevation(),
                it.getGroundwaterLevel(), it.getCreateUser(), it.getCreateTime(), it.getIsDelete(), it.getProjectName(),it.getDataType(),it.getName()
        );
    }

    @Override
    public void saveApCruxSc(DzqhApCruxScEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_sc(sc_distinguish_id,project_id,borehole_number,eswv,toc_layer,field_type,longitude,latitude,pf_id,del_flag)" +
                "values (?,?,?,?,?,?,?,?,?,?)", it.getScDistinguishId(), it.getProjectId(), it.getBoreholeNumber(), it.getEswv(), it.getTocLayer(), it.getFieldType(), it.getLongitude(), it.getLatitude(),it.getPfId(),it.getDelFlag());
    }

    @Override
    public void saveApCruxSd(DzqhApCruxSdEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_sd(sd_id,project_id,st_number,geotechnical_name,borehole_number,bdos_sample,parameter_source,sd_density,modulus_ratio1," +
                        "modulus_ratio2,modulus_ratio3,modulus_ratio4,modulus_ratio5,modulus_ratio6,modulus_ratio7,modulus_ratio8,damping_ratio1,damping_ratio2,damping_ratio3,damping_ratio4," +
                        "damping_ratio5,damping_ratio6,damping_ratio7,damping_ratio8,del_flag)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                it.getSdId(), it.getProjectId(), it.getStNumber(), it.getGeotechnicalName(), it.getBoreholeNumber(), it.getBdosSample(), it.getParameterSource(), it.getSdDensity(), it.getModulusRatio1(),
                it.getModulusRatio2(), it.getModulusRatio3(), it.getModulusRatio4(), it.getModulusRatio5(), it.getModulusRatio6(), it.getModulusRatio7(), it.getModulusRatio8(), it.getDampingRatio1(),
                it.getDampingRatio2(), it.getDampingRatio3(), it.getDampingRatio4(), it.getDampingRatio5(), it.getDampingRatio6(), it.getDampingRatio7(), it.getDampingRatio8(),it.getDelFlag());
    }

    @Override
    public void saveApCruxSgm(DzqhApCruxSgmEntity it) {
        jdbcTemplate.update("insert into dzqh_ap_crux_sgm(sgm_id,project_id,transcend_probability,amax_gal,bm,amax,t1_s,tg_s,v,damping_ratio,borehole_number,del_flag) values (?,?,?,?,?,?,?,?,?,?,?,?)",
                it.getSgmId(), it.getProjectId(), it.getTranscendProbability(), it.getAmaxGal(), it.getBm(), it.getAmax(), it.getT1S(), it.getTgS(), it.getV(), it.getDampingRatio(), it.getBoreholeNumber(),it.getDelFlag());
    }

    @Override
    public DzqhApAsicMapEntity queryDzqhApAsicMap(String projectId) {
        String sql = "select * from dzqh_ap_basic_map where project_id = '" + projectId + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }
    @Override
    public List<DzqhApCruxDhEntity> queryDzqhApCruxDh(String projectId) {
        String sql = "select * from dzqh_ap_crux_dh where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxDhEntity.class));
    }

    @Override
    public List<DzqhApCruxElEntity> queryDzqhApCruxEl(String projectId) {
        String sql = "select * from dzqh_ap_crux_el where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxElEntity.class));
    }

    @Override
    public List<DzqhApCruxHecEntity> queryDzqhApCruxHec(String projectId) {
        String sql = "select * from dzqh_ap_crux_hec where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxHecEntity.class));
    }

    @Override
    public List<DzqhApCruxLayerPfEntity> queryDzqhApCruxLayerPf(String projectId) {
        String sql = "select dabm.project_name,pf.* from dzqh_ap_crux_layer_pf pf left join dzqh_ap_basic_map dabm on pf.project_id=dabm.project_id  where pf.project_id =  '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
    }

    @Override
    public List<DzqhApCruxScEntity> queryDzqhApCruxSc(String projectId) {
        String sql = "select * from dzqh_ap_crux_sc where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxScEntity.class));
    }

    @Override
    public List<DzqhApCruxSdEntity> queryDzqhApCruxSd(String projectId) {
        String sql = "select * from dzqh_ap_crux_sd where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxSdEntity.class));
    }

    @Override
    public List<DzqhApCruxSgmEntity> queryDzqhApCruxSgm(String projectId) {
        String sql = "select * from dzqh_ap_crux_sgm where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxSgmEntity.class));
    }

    @Override
    public HashMap<String, Object> getSaResultsProject(DzqhApAsicMapEntity mapEntity, int page, int size, String orgId, String provinceId, String userId,String geom) {
        StringBuilder sql = new StringBuilder("select * from dzqh_ap_basic_map where sub_status='1' and update_status='0'");
        if (mapEntity.getRole().equals("3")) {
            sql.append(" and is_delete='0' and create_user='" + userId + "'");
        }
        if (mapEntity.getRole().equals("1")) {
            sql.append(" and province_id='" + provinceId + "'");
        }
        if (mapEntity.getRole().equals("2")) {
            //sql.append(" and province_id='"+provinceId +"' or org_id='"+orgId+"'");
            sql.append(" and (province_id='" + provinceId + "' or org_id='" + orgId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getProjectName())) {
            sql.append(" and project_name like '%" + mapEntity.getProjectName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getLongitude())) {
            sql.append(" and longitude >= '" + mapEntity.getLongitude() +"'");
        }
        if(!PlatformObjectUtils.isEmpty(mapEntity.getEndLongitude())){
            sql.append(" and longitude <= '" + mapEntity.getEndLongitude() +"'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getLatitude())) {
            sql.append(" and latitude >= '" + mapEntity.getLatitude() + "'");
        }
        if(!PlatformObjectUtils.isEmpty(mapEntity.getEndLatitude())){
            sql.append(" and latitude <= '" + mapEntity.getEndLatitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getApprovalStatus())) {
            sql.append(" and approval_status ='" + mapEntity.getApprovalStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getReviewUnit())) {
            sql.append(" and review_unit like '%" + mapEntity.getReviewUnit() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getSafetyRating())) {
            sql.append(" and safety_rating ='" + mapEntity.getSafetyRating() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getStatus())) {
            if("0".equals(mapEntity.getStatus())){
                sql.append(" and status is null ");
            }else{
                sql.append(" and status = '" + mapEntity.getStatus() + "'");
            }

        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getAdministrativeRegion())) {
            sql.append(" and administrative_region= '" + mapEntity.getAdministrativeRegion() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getUndertaker())) {
            sql.append(" and undertaker like '%" + mapEntity.getUndertaker() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrant())) {
            sql.append(" and registrant like '%" + mapEntity.getRegistrant() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getRegistrationUnit())) {
            sql.append(" and registration_unit like '%" + mapEntity.getRegistrationUnit() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getCreateStartTime())) {
            sql.append(" and date(create_time) >= '"+mapEntity.getCreateStartTime()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(mapEntity.getCreateEndTime())){
            sql.append(" and date(create_time) <= '"+mapEntity.getCreateEndTime()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(mapEntity.getSubStartTime()) && !PlatformObjectUtils.isEmpty(mapEntity.getSubEndTime())) {
            sql.append(" and sub_date >= '" + mapEntity.getSubStartTime() + "' and sub_date <='" + mapEntity.getSubEndTime() + "'");
        }

        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(geom)) {
            //项目位置上传文件空间位置相交
            sql.append(" and geom_text is not null and geom_text!='' and ST_Intersects(st_geometryfromtext(geom_text,4490),st_geometryfromtext('"+geom+"',4490))");
            //项目位置经纬度相交查询
            //sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
        }

        sql.append(" group by project_id");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(orderSql).append(limitSql);
        List<DzqhApAsicMapEntity> apAsicMapList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class));
        for (DzqhApAsicMapEntity asicMap : apAsicMapList) {
            String region = asicMap.getAdministrativeRegion();
            asicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", apAsicMapList);
        return resultMap;
    }

    @Override
    public void updateApAsicMap(DzqhApAsicMapEntity it) {
        String sql = "update dzqh_ap_basic_map set project_name= :projectName,survey= :survey," +
                "administrative_region= :administrativeRegion,review_unit= :reviewUnit,undertaker= :undertaker," +
                "project_type= :projectType,safety_rating= :safetyRating,control_points= :controlPoints,evaluation_results= :evaluationResults," +
                "registration_unit= :registrationUnit,rs_map= :rsMap,fp_map= :fpMap,nfss_map= :nfssMap,reed_map= :reedMap,nfeed_map= :nfeedMap," +
                "psa_map= :psaMap,bh_map= :bhMap,seismic_pz= :seismicPz,bd_map= :bdMap,eg_profile= :egProfile,sagh_evaluation= :saghEvaluation," +
                "lp_picture= :lpPicture,aosp_file= :aospFile,periodic_point= :periodicPoint,rs_value= :rsValue," +
                "upload_report= :uploadReport,update_user= :updateUser,update_reason= :updateReason,update_time= :updateTime,client= :client,project_type_remarks= :projectTypeRemarks,cp_file= :cpFile," +
                "status= :status,sub_status= :subStatus,longitude= :longitude,latitude= :latitude,sub_date= :subDate,geological_profile_image= :geologicalProfileImage,update_detailed_information = :updateDetailedInformation,compare = :compare," +
                "geom_text= :geomText" +
                " where project_id = :projectId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCruxDh(DzqhApCruxDhEntity it) {
        String sql = "update dzqh_ap_crux_dh set geotechnical_name= :geotechnicalName,st_number= :stNumber,lb_depth= :lbDepth,geotechnical_density= :geotechnicalDensity,layer_thickness= :layerThickness," +
                "sw_velocity= :swVelocity,del_flag = :delFlag where geotechnical_id= :geotechnicalId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCzruxEl(DzqhApCruxElEntity it) {
        String sql = "update dzqh_ap_crux_el set test_number= :testNumber,test_ar= :testAr,construction_unit= :constructionUnit,start_longitude= :startLongitude," +
                "start_latitude =:startLatitude,end_longitude= :endLongitude,end_latitude= :endLatitude," +
                "test_length =:testLength,pposl_map=:pposlMap,survey_method=:surveyMethod,survey_parameters = :surveyParameters,del_flag = :delFlag where test_id= :testId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCruxHec(DzqhApCruxHecEntity it) {
        String sql = "update dzqh_ap_crux_hec set eo_time_ymd= :eoTimeYmd,eo_time_hms= :eoTimeHms,north_latitude= :northLatitude,east_longitude= :eastLongitude,magnitude= :magnitude,depth= :depth," +
                "epicentral_intensity= :epicentralIntensity,accuracy= :accuracy," +
                "erp_name= :erpName,del_flag = :delFlag where ec_id= :ecId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCruxLayerPf(DzqhApCruxLayerPfEntity it) {
        String sql = "update dzqh_ap_crux_layer_pf set borehole_number= :boreholeNumber,hl_longitude= :hlLongitude,hl_latitude= :hlLatitude," +
                "drilling_depth= :drillingDepth,ground_elevation= :groundElevation,groundwater_level= :groundwaterLevel,update_user= :updateUser," +
                "update_time= :updateTime,project_name= :projectName,name = :name,is_delete = :isDelete where pf_id= :pfId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCruxSc(DzqhApCruxScEntity it) {
        String sql = "update dzqh_ap_crux_sc set borehole_number= :boreholeNumber,eswv= :eswv,toc_layer= :tocLayer,field_type= :fieldType,del_flag = :delFlag" +
                " where sc_distinguish_id= :scDistinguishId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCruxSd(DzqhApCruxSdEntity it) {
        String sql = "update dzqh_ap_crux_sd set st_number= :stNumber,geotechnical_name= :geotechnicalName,borehole_number= :boreholeNumber,bdos_sample= :bdosSample,parameter_source= :parameterSource,sd_density= :sdDensity,modulus_ratio1= :modulusRatio1," +
                "modulus_ratio2= :modulusRatio2,modulus_ratio3= :modulusRatio3,modulus_ratio4= :modulusRatio4,modulus_ratio5= :modulusRatio5,modulus_ratio6= :modulusRatio6,modulus_ratio7= :modulusRatio7,modulus_ratio8= :modulusRatio8,damping_ratio1= :dampingRatio1,damping_ratio2= :dampingRatio3,damping_ratio3= :dampingRatio3,damping_ratio4= :dampingRatio4," +
                "damping_ratio5= :dampingRatio5,damping_ratio6= :dampingRatio6,damping_ratio7= :dampingRatio7,damping_ratio8= :dampingRatio8,del_flag = :delFlag where sd_id= :sdId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateApCruxSgm(DzqhApCruxSgmEntity it) {
        String sql = "update dzqh_ap_crux_sgm set transcend_probability= :transcendProbability,amax_gal= :amaxGal,bm= :bm,amax= :amax," +
                "t1_s= :t1S,tg_s= :tgS,v= :v,damping_ratio= :dampingRatio,borehole_number= :boreholeNumber,del_flag = :delFlag where sgm_id= :sgmId";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(it);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteDzqhApAsicMap(String projectId, String deleteReason) {
        String sql = "update dzqh_ap_basic_map set delete_reason= ?,status='3', approval_status='1' where project_id = ?";
        jdbcTemplate.update(sql, deleteReason, projectId);
    }

    @Override
    public HashMap<String, Object> getSaResultsHl(SaResultsHlVo saResultsHlVo, int page, int size, String orgId, String provinceId, String userId,String geom) {
        StringBuilder sql = new StringBuilder("SELECT dam.* FROM dzqh_ap_basic_map dam LEFT JOIN ( SELECT * FROM dzqh_ap_crux_layer_pf GROUP BY pf_id ) AS daclp ON daclp.project_id = dam.project_id LEFT JOIN dzqh_ap_crux_sd dacsd ON dacsd.project_id = dam.project_id LEFT JOIN dzqh_ap_crux_sc dacsc ON dacsc.project_id = dam.project_id LEFT JOIN dzqh_ap_crux_sd dacs ON dacs.project_id = dam.project_id WHERE dam.sub_status = '1' AND dam.update_status = '0'");
        if (saResultsHlVo.getRole().equals("3")) {
            sql.append(" and dam.is_delete='0' and dam.create_user='" + userId + "'");
        }
        if (saResultsHlVo.getRole().equals("1")) {
            sql.append(" and dam.province_id='" + provinceId + "'");
        }
        if (saResultsHlVo.getRole().equals("2")) {
            //sql.append(" and dam.province_id='"+provinceId +"' or dam.org_id='"+orgId+"'");
            sql.append(" and (dam.province_id='" + provinceId + "' or dam.org_id='" + orgId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getProjectName())) {
            sql.append(" and dam.project_name like '%" + saResultsHlVo.getProjectName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLongitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLongitude())) {
            sql.append(" and daclp.hl_longitude between '" + saResultsHlVo.getHlLongitude() + "' and '" + saResultsHlVo.getEndHlLongitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getHlLatitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndHlLatitude())) {
            sql.append(" and daclp.hl_latitude between '" + saResultsHlVo.getHlLatitude() + "' and '" + saResultsHlVo.getEndHlLatitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getBdosSample())) {
            sql.append(" and dacsd.bdos_sample = '" + saResultsHlVo.getBdosSample() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getApprovalStatus())) {
            sql.append(" and dam.approval_status ='" + saResultsHlVo.getApprovalStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getStatus())) {
            sql.append(" and dam.status = '" + saResultsHlVo.getStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getLongitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndLongitude())) {
            sql.append(" and dam.longitude between '" + saResultsHlVo.getLongitude() + "' and '" + saResultsHlVo.getEndLongitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getLatitude()) && !PlatformObjectUtils.isEmpty(saResultsHlVo.getEndLatitude())) {
            sql.append(" and dam.latitude between '" + saResultsHlVo.getLatitude() + "' and '" + saResultsHlVo.getEndLatitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getGeotechnicalName())) {
            sql.append(" and dacs.geotechnical_name like '%" + saResultsHlVo.getGeotechnicalName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(saResultsHlVo.getFieldType())) {
            sql.append(" and dacsc.field_type = '" + saResultsHlVo.getFieldType() + "'");
        }

        //有孔位信息
        sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(geom)) {
            //项目信息相交
            //sql.append(" and dam.geom_text is not null and dam.geom_text!='' and ST_Intersects(st_geometryfromtext(dam.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
            //孔位信息相交
            sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

        StringBuilder orderSql = new StringBuilder(" group by dam.project_id order by dam.create_time desc");
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(limitSql);
        List<DzqhApAsicMapEntity> apAsicMapList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class));
        for (DzqhApAsicMapEntity asicMap : apAsicMapList) {
            String region = asicMap.getAdministrativeRegion();
            asicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", apAsicMapList);
        return resultMap;
    }

    @Override
    public void updateReason(String updateReason, String projectId) {
        String sql = "update dzqh_ap_basic_map set status='1',update_reason=? where project_id=?";
        jdbcTemplate.update(sql, updateReason, projectId);
    }

    @Override
    public void saveJournal(DzqhJournalEntity journalEntity) {
        jdbcTemplate.update("insert into dzqh_journal(journal_id,project_id,operation,field,before_updating,after_update,operation_user,operation_time,status,table_name,company,user_name) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?)", journalEntity.getJournalId(), journalEntity.getProjectId(), journalEntity.getOperation(), journalEntity.getField(), journalEntity.getBeforeUpdating(),
                journalEntity.getAfterUpdate(), journalEntity.getOperationUser(), journalEntity.getOperationTime(), journalEntity.getStatus(), journalEntity.getTableName(), journalEntity.getCompany(), journalEntity.getUserName());
    }

    @Override
    public List<DzqhJournalEntity> queryHistory(String originalDataId, String status) {
        String sql = "select * from dzqh_journal where project_id = '" + originalDataId + "' and status='" + status + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhJournalEntity.class));
    }

    @Override
    public List<DzqhApAsicMapEntity> queryNotSubmitted(String userId) {
        String sql = "select * from dzqh_ap_basic_map where sub_status = '0' and create_user =?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class), userId);
    }

    @Override
    public void updateDzqhApAsicMapStatus(String projectId, String deleteReason, String status) {
        String sql = "update dzqh_ap_basic_map set delete_reason= ?, status = ? where project_id = ?";
        jdbcTemplate.update(sql, deleteReason, status, projectId);
    }

    @Override
    public void updateApprovalStatus(String status, String projectId, String reviewComments, String approvalStatus, String userName, String orgName, Date approvalTime) {
        String sql = "update dzqh_ap_basic_map set status=?, approval_status= ?, review_comments = ?,update_status='0',approval_user=?,approval_time=?,approval_org=? where original_data_id = ?";
        jdbcTemplate.update(sql, status, approvalStatus, reviewComments, userName, approvalTime, orgName, projectId);
    }

    @Override
    public DzqhApCruxDhEntity queryDzqhApCruxDhById(String geotechnicalId) {
        String sql = "select * from dzqh_ap_crux_dh where geotechnical_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxDhEntity.class), geotechnicalId);
    }

    @Override
    public DzqhApCruxElEntity queryCruxElById(String testId) {
        String sql = "select * from dzqh_ap_crux_el where test_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxElEntity.class), testId);
    }

    @Override
    public DzqhApCruxHecEntity queryCruxHecById(String ecId) {
        String sql = "select * from dzqh_ap_crux_hec where ec_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxHecEntity.class), ecId);
    }

    @Override
    public DzqhApCruxLayerPfEntity queryCruxLayerPfById(String pfId) {
        String sql = "select * from dzqh_ap_crux_layer_pf where pf_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class), pfId);
    }

    @Override
    public DzqhApCruxScEntity queryCruxScById(String scDistinguishId) {
        String sql = "select * from dzqh_ap_crux_sc where sc_distinguish_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxScEntity.class), scDistinguishId);
    }

    @Override
    public DzqhApCruxSdEntity queryCruxSdById(String sdId) {
        String sql = "select * from dzqh_ap_crux_sd where sd_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxSdEntity.class), sdId);
    }

    @Override
    public DzqhApCruxSgmEntity queryCruxSgmById(String sgmId) {
        String sql = "select * from dzqh_ap_crux_sgm where sgm_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhApCruxSgmEntity.class), sgmId);
    }

    @Override
    public List<DzqhApAsicMapEntity> queryAllProject() {
        String sql = "select * from dzqh_ap_basic_map where is_delete = '0' and sub_status='1' and update_status='0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class));
    }

    @Override
    public void deleteApAsicMap(String projectId) {
        String sql = "delete from dzqh_ap_basic_map where project_id=?";
        jdbcTemplate.update(sql, projectId);
    }

    @Override
    public void updateJournalStatus(String projectId, String status) {
        String sql = "update dzqh_journal set status= ? where project_id = ? and status = '0'";
        jdbcTemplate.update(sql, status, projectId);
    }


    @Override
    public void approvalUserDelete(String projectId, String reviewComments, String approvalStatus, String status, String isDelete, String userName, String orgName, Date approvalTime) {
        String sql = "update dzqh_ap_basic_map set approval_status=?,status=?,is_delete=?,review_comments=?,approval_user=?,approval_time=?,approval_org=? where project_id=?";
        jdbcTemplate.update(sql, approvalStatus, status, isDelete, reviewComments, userName, approvalTime, orgName, projectId);
    }

    @Override
    public List<DzqhApAsicMapEntity> queryProjectByProjectName(String projectName) {
        String sql = "select * from dzqh_ap_basic_map where is_delete = '0' and sub_status='1' and update_status='0' and project_name='" + projectName + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApAsicMapEntity.class));
    }

    @Override
    public void tombstoneApAsicMap(String projectId, String deleteReason) {
        String sql = "update dzqh_ap_basic_map set delete_reason= ?, approval_status='1',status = 3 where project_id = ?";
        jdbcTemplate.update(sql, deleteReason, projectId);
    }

    @Override
    public void updateOldStatus(String projectId, String updateReason) {
        String sql = "update dzqh_ap_basic_map set status='2', approval_status= '1' ,update_reason= ? where project_id = ?";
        jdbcTemplate.update(sql, updateReason, projectId);
    }

    @Override
    public void auditFailed(String status, String projectId, String reviewComments, String approvalStatus, String userName, String orgName, Timestamp currentTimestamp) {
        String sql = "update dzqh_ap_basic_map set status=?, approval_status= ?, review_comments = ?,update_status='0',approval_user=?,approval_time=?,approval_org=? where project_id = ?";
        jdbcTemplate.update(sql, status, approvalStatus, reviewComments, userName, currentTimestamp, orgName, projectId);
    }

    @Override
    public void updateReasonAdmin(String updateReason, String projectId) {
        String sql = "update dzqh_ap_basic_map set status='1',update_reason=?,approval_status='1' where project_id=?";
        jdbcTemplate.update(sql, updateReason, projectId);
    }

    @Override
    public void saveProfileInfo(DzqhProfileInformationEntity it) {
        jdbcTemplate.update("insert into dzqh_profile_information  (bisect_id,bisect_name,section_area,longitude,latitude,project_id,pposl_map) values (?,?,?,?,?,?,?)",
                it.getBisectId(), it.getBisectName(), it.getSectionArea(), it.getLongitude(), it.getLatitude(),it.getProjectId(),it.getPposlMap());
    }

    @Override
    public void updateProfileInfo(DzqhProfileInformationEntity it) {
        jdbcTemplate.update("update  dzqh_profile_information  set bisect_name=?,section_area=?,longitude=?,latitude=?,pposl_map = ?,del_flag = ?"
                        + " where bisect_id=?",
                it.getBisectName(), it.getSectionArea(), it.getLongitude(), it.getLatitude(),it.getPposlMap(),it.getDelFlag(),
                it.getBisectId()
        );
    }

    @Override
    public DzqhProfileInformationEntity queryprofileInfoById(String bisectId) {
        String sql = "select * from dzqh_profile_information where bisect_id =?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DzqhProfileInformationEntity.class), bisectId);
    }

    @Override
    public List<DzqhProfileInformationEntity> queryprofileInfo(String projectId) {
        String sql = "select * from dzqh_profile_information where project_id = '" + projectId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhProfileInformationEntity.class));
    }

    @Override
    public List<DzqhApCruxLayerPfEntity> getAllPf() {
        String sql = "select * from dzqh_ap_crux_layer_pf where is_delete = '0' and data_type = '普查数据'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxLayerPfEntity.class));
    }

    @Override
    public void update(String pfId, String name) {
        String sql = "update dzqh_ap_crux_sd set parameter_source = '"+name+"' where pf_id='"+pfId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<DzqhApCruxDhEntity> getDh(String pfId) {
        String sql = "select * from dzqh_ap_crux_dh where pf_id = '"+pfId+"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxDhEntity.class));
    }

    @Override
    public List<DzqhApCruxScEntity> getSc(String pfId) {
        String sql = "select * from dzqh_ap_crux_sc where pf_id = '"+pfId+"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzqhApCruxScEntity.class));
    }

    @Override
    public void update1(double shearWaveVelocityValue, double eswv, String pfId) {
        String sql = "update dzqh_ap_crux_sc set eswv = '"+eswv+"' ,toc_layer = '"+shearWaveVelocityValue+"' where pf_id='"+pfId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void adminUpdate(AdminUpdateParam param) {
        String tableName = "dzqh_ap_basic_map";
        if(param.getType() == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(param.getType() == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "update "+tableName+" set status = '1',update_reason = '"+param.getReason()+"' where project_id = '"+param.getId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteById(AdminUpdateParam param,String status) {
        String tableName = "dzqh_ap_basic_map";
        if(param.getType() == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(param.getType() == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "update "+tableName+" set status = '3',delete_reason = '"+param.getReason()+"',approval_status = "+status+" where project_id = '"+param.getId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateDataStatusByOriginalDataId(ApproveParam param, String userName, Timestamp time, String orgName) {
        String tableName = "dzqh_ap_basic_map";
        if(param.getType() == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(param.getType() == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "update "+tableName+" set status = '2',update_status = '0', approval_status = '"+param.getStatus()+"',review_comments = '"+param.getOpinion()+"',approval_user = '"+userName+"',approval_time = '"+time+"',approval_org = '"+orgName+"' where original_data_id = '"+param.getId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteDataById(String id,Integer type) {
        String tableName = "dzqh_ap_basic_map";
        if(type == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(type == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "delete from "+tableName+" where project_id = '"+id+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteDataByOriginalDataId(String id,Integer type) {
        String tableName = "dzqh_ap_basic_map";
        if(type == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(type == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "delete from "+tableName+" where original_data_id = '"+id+"' and update_status = '1'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateStatusById(ApproveParam param, String userName, Timestamp time, String orgName,String status) {
        String tableName = "dzqh_ap_basic_map";
        if(param.getType() == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(param.getType() == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "update "+tableName+" set status = '"+status+"', approval_status = '"+param.getStatus()+"',review_comments = '"+param.getOpinion()+"',approval_user = '"+userName+"',approval_time = '"+time+"',approval_org = '"+orgName+"' where project_id = '"+param.getId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateStatus(DzqhApAsicMapEntity entity, String projectId) {
        String sql = "update dzqh_ap_basic_map set update_reason = '"+entity.getUpdateReason()+"',status = '"+entity.getStatus()+"',approval_status = '"+entity.getApprovalStatus()+"' where project_id = '"+projectId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<DzqhProfileInformationEntity> queryProfileInformationList(String projectId) {
        String sql = "select * from dzqh_profile_information where project_id = '"+projectId+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DzqhProfileInformationEntity.class));
    }

    @Override
    public void updateDataStatusById(ApproveParam param, String userName, Timestamp time, String orgName) {
        String tableName = "dzqh_ap_basic_map";
        if(param.getType() == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(param.getType() == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "update "+tableName+" set status = '1',update_status = '0', approval_status = '"+param.getStatus()+"',review_comments = '"+param.getOpinion()+"',approval_user = '"+userName+"',approval_time = '"+time+"',approval_org = '"+orgName+"' where project_id = '"+param.getId()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<HistoryVo> getHistory(String id, Integer type) {
        StringBuilder sql = new StringBuilder("select * from dzqh_journal where project_id = '"+id+"' ");
        if(type == 1){
            sql.append(" and status = '0'");
        }
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(HistoryVo.class));
    }

    @Override
    public void deleteDataByOriginalDataIdAndStatus(String projectId) {
        String sql = "delete from dzqh_ap_basic_map where original_data_id = '"+projectId+"' and sub_status = '0' ";
        jdbcTemplate.update(sql);
    }

    @Override
    public String queryLogId(String id, Integer type) {
        String tableName = "dzqh_ap_basic_map";
        if(type == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(type == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "select log_id from "+tableName+" where project_id = '"+id+"'";
        return jdbcTemplate.queryForObject(sql,String.class);
    }

    @Override
    public void deleteDhByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_dh set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_dh where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteElByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_el set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_el where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteHecByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_hec set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_hec where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deletePfByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_layer_pf set is_delete = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_layer_pf where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteScByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_sc set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_sc where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteSdByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_sd set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_sd where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteSgmByProjectId(String id,Integer type) {
        String sql = "update dzqh_ap_crux_sgm set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 3){
            sql = "delete from dzqh_ap_crux_sgm where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteProByProjectId(String id,Integer type) {
        String sql = "update dzqh_profile_information set del_flag = '"+type+"' where project_id = '"+id+"'";
        if(type == 0){
            sql = "delete from dzqh_profile_information where project_id = '"+id+"'";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public String queryDzqhApBasicMapByOldId(String id, Integer type) {
        String tableName = "dzqh_ap_basic_map";
        if(type == 2){
            tableName = "dzqh_qp_basic_map";
        }else if(type == 3){
            tableName = "dzqh_xqh_basic_map";
        }
        String sql = "select project_id from "+tableName+" where original_data_id = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (DataAccessException e) {
            return null;
        }
    }
}
